﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Isaac.Joy.Identity.Module;
using Isaac.Joy.StringHelper;

namespace Isaac.Joy.Identity.Repository.SqlClient
{
    public class Repository : IRepository
    {
        public User AddUser(User user)
        {
            if (user.CreateTime == new DateTime()) user.CreateTime = DateTime.Now;
            user.Password = MD5.Encrypt(user.Password);

            var sbSql = new StringBuilder();
            sbSql.AppendLine("INSERT INTO Isaac_Joy_Identity_User");
            sbSql.AppendLine("(");
            sbSql.AppendLine("  Account");
            sbSql.AppendLine("  ,Password");
            sbSql.AppendLine("  ,Email");
            sbSql.AppendLine("  ,NickName");
            sbSql.AppendLine("  ,Birthday");
            sbSql.AppendLine("  ,Mobile");
            sbSql.AppendLine("  ,Tel");
            sbSql.AppendLine("  ,QQ");
            sbSql.AppendLine("  ,Address");
            sbSql.AppendLine("  ,Gender");
            sbSql.AppendLine("  ,CreateTime");
            sbSql.AppendLine("  ,UpdateTime");
            sbSql.AppendLine("  ,IsDelete");
            sbSql.AppendLine("  ,Roleid");
            sbSql.AppendLine(")");
            sbSql.AppendLine("VALUES");
            sbSql.AppendLine("(");
            sbSql.AppendLine("  @Account");
            sbSql.AppendLine("  ,@Password");
            sbSql.AppendLine("  ,@Email");
            sbSql.AppendLine("  ,@NickName");
            sbSql.AppendLine("  ,@Birthday");
            sbSql.AppendLine("  ,@Mobile");
            sbSql.AppendLine("  ,@Tel");
            sbSql.AppendLine("  ,@QQ");
            sbSql.AppendLine("  ,@Address");
            sbSql.AppendLine("  ,@Gender");
            sbSql.AppendLine("  ,@CreateTime");
            sbSql.AppendLine("  ,@UpdateTime");
            sbSql.AppendLine("  ,@IsDelete");
            sbSql.AppendLine("  ,@Roleid");
            sbSql.AppendLine(");");
            sbSql.AppendLine("SELECT @@IDENTITY;");

            var id = Database.Instence.ExecuteScalar<int>(sbSql.ToString(), new
            {
                Account = user.Account ?? "",
                Password = user.Password ?? "",
                Email = user.Email ?? "",
                NickName = user.NickName ?? "",
                Birthday = user.Birthday ?? user.CreateTime,
                Mobile = user.Mobile ?? "",
                Tel = user.Tel ?? "",
                QQ = user.QQ ?? "",
                Address = user.Address ?? "",
                Gender = user.Gender,
                CreateTime = user.CreateTime,
                UpdateTime = user.UpdateTime ?? user.CreateTime,
                IsDelete = 0,
                Roleid = user.Role.Id,
            });

            if (id <= 0) return null;

            user.Id = id;
            return user;
        }

        public User GetUser(int id)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT");
            sbSql.AppendLine("	#User.*");
            sbSql.AppendLine("	,#Role.*");
            sbSql.AppendLine("FROM Isaac_Joy_Identity_User AS #User");
            sbSql.AppendLine("	INNER JOIN Isaac_Joy_Identity_Role AS #Role");
            sbSql.AppendLine("		ON #User.Roleid = #Role.Id");
            sbSql.AppendLine("WHERE");
            sbSql.AppendLine("	#User.Id = @Id");

            return Database.Instence.Query<User, Role, User>(
                sbSql.ToString(),
                (user, role) =>
                {
                    user.Role = role;
                    return user;
                },
                new
                {
                    Id = id
                }).FirstOrDefault();
        }

        public User AuthorizeUser(string loginid, string pwd)
        {
            var password = MD5.Encrypt(pwd);

            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT");
            sbSql.AppendLine("	#User.*");
            sbSql.AppendLine("	,#Role.*");
            sbSql.AppendLine("FROM Isaac_Joy_Identity_User AS #User");
            sbSql.AppendLine("	INNER JOIN Isaac_Joy_Identity_Role AS #Role");
            sbSql.AppendLine("		ON #User.Roleid = #Role.Id");
            sbSql.AppendLine("WHERE #User.IsDelete = 0");
            sbSql.AppendLine("	AND #User.Account = @Account");
            sbSql.AppendLine("	AND #User.Password = @Password");

            return Database.Instence.Query<User, Role, User>(
                sbSql.ToString(),
                (user, role) =>
                {
                    user.Role = role;
                    return user;
                },
                new
                {
                    Account = loginid,
                    Password = password
                }).FirstOrDefault();
        }

        public bool EditUser(User user)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("UPDATE Isaac_Joy_Identity_User");
            sbSql.AppendLine("SET");
            sbSql.AppendLine("  Account=@Account");
            sbSql.AppendLine("  ,Email=@Email");
            sbSql.AppendLine("  ,NickName=@NickName");
            if (user.Birthday != null)
            {
                sbSql.AppendLine("  ,Birthday=@Birthday");
            }
            sbSql.AppendLine("  ,Mobile=@Mobile");
            sbSql.AppendLine("  ,Tel=@Tel");
            sbSql.AppendLine("  ,QQ=@QQ");
            sbSql.AppendLine("  ,Address=@Address");
            sbSql.AppendLine("  ,Gender=@Gender");
            sbSql.AppendLine("  ,UpdateTime=@UpdateTime");
            sbSql.AppendLine("  ,IsDelete=@IsDelete");
            sbSql.AppendLine("  ,Roleid=@Roleid");
            sbSql.AppendLine("WHERE");
            sbSql.AppendLine("  Id=@Id");

            var rowcount = Database.Instence.Execute(sbSql.ToString(), new
            {
                Account = user.Account ?? "",
                Email = user.Email ?? "",
                NickName = user.NickName ?? "",
                Birthday = user.Birthday,
                Mobile = user.Mobile ?? "",
                Tel = user.Tel ?? "",
                QQ = user.QQ ?? "",
                Address = user.Address ?? "",
                Gender = user.Gender,
                UpdateTime = DateTime.Now,
                IsDelete = 0,
                Roleid = user.Role.Id,
                Id = user.Id
            });

            return rowcount > 0;
        }

        public int DeleteUser(int[] ids)
        {
            var strid = ids.Aggregate(string.Empty, (current, id) => current + (id + ", "));

            var sbSql = new StringBuilder();
            sbSql.AppendLine(String.Format("UPDATE Isaac_Joy_Identity_User SET IsDelete = 1 WHERE Id IN ({0} 0)", strid));

            return Database.Instence.Execute(sbSql.ToString());
        }

        public IEnumerable<User> QueryUser(int pageindex, int pagesize, out int datacount, string wd = null, int? rid = 0)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT COUNT(1) ");
            sbSql.AppendLine("FROM Isaac_Joy_Identity_User");
            sbSql.AppendLine("  INNER JOIN Isaac_Joy_Identity_Role");
            sbSql.AppendLine("    ON Isaac_Joy_Identity_User.Roleid = Isaac_Joy_Identity_Role.Id");
            sbSql.AppendLine("WHERE Isaac_Joy_Identity_User.IsDelete = 0");
            sbSql.AppendLine("AND (Account like @KeyWord or @KeyWord = '')");
            sbSql.AppendLine("AND (Roleid = @Roleid or @Roleid = 0)");

            datacount = Database.Instence.ExecuteScalar<int>(sbSql.ToString(),
                new
                {
                    KeyWord = wd == null ? "" : "%" + wd + "%",
                    Roleid = rid ?? 0
                });

            sbSql.Clear();

            sbSql.AppendLine("SELECT");
            sbSql.AppendLine("  #User.*,");
            sbSql.AppendLine("  #Role.*");
            sbSql.AppendLine("FROM");
            sbSql.AppendLine("(");
            sbSql.AppendLine("  SELECT");
            sbSql.AppendLine("    Isaac_Joy_Identity_User.*,");
            sbSql.AppendLine("    ROW_NUMBER() OVER (ORDER BY Isaac_Joy_Identity_User.CreateTime DESC) AS [Row]");
            sbSql.AppendLine("  FROM Isaac_Joy_Identity_User");
            sbSql.AppendLine("    INNER JOIN Isaac_Joy_Identity_Role");
            sbSql.AppendLine("      ON Isaac_Joy_Identity_User.Roleid = Isaac_Joy_Identity_Role.Id");
            sbSql.AppendLine("WHERE Isaac_Joy_Identity_User.IsDelete = 0");
            sbSql.AppendLine("  AND (Isaac_Joy_Identity_User.Account like @KeyWord or @KeyWord = '')");
            sbSql.AppendLine("  AND (Isaac_Joy_Identity_User.Roleid = @Roleid or @Roleid = 0)");
            sbSql.AppendLine(") as #User");
            sbSql.AppendLine("	INNER JOIN Isaac_Joy_Identity_Role as #Role");
            sbSql.AppendLine("	ON #User.Roleid = #Role.Id");
            sbSql.AppendLine("WHERE");
            sbSql.AppendLine("  #User.[Row] BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize");
            sbSql.AppendLine("ORDER BY #User.[Row]");

            return Database.Instence.Query<User, Role, User>(
                sbSql.ToString(),
                (user, role) =>
                {
                    user.Role = role;
                    return user;
                },
                new
                {
                    KeyWord = wd == null ? "" : "%" + wd + "%",
                    Roleid = rid ?? 0,
                    PageIndex = pageindex,
                    PageSize = pagesize
                });
        }

        public IEnumerable<Role> GetRoles()
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT * FROM Isaac_Joy_Identity_Role WHERE IsDelete = 0");
            return Database.Instence.Query<Role>(sbSql.ToString());
        }

        public Role AddRole(Role role)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("INSERT INTO Isaac_Joy_Identity_Role");
            sbSql.AppendLine("(");
            sbSql.AppendLine("  Name");
            sbSql.AppendLine("  ,Value");
            sbSql.AppendLine(")");
            sbSql.AppendLine("VALUES");
            sbSql.AppendLine("(");
            sbSql.AppendLine("  @Name");
            sbSql.AppendLine("  ,@Value");
            sbSql.AppendLine(");");
            sbSql.AppendLine("SELECT @@IDENTITY;");

            var id = Database.Instence.ExecuteScalar<int>(sbSql.ToString(), new
            {
                Name = role.Name ?? "",
                Value = string.IsNullOrEmpty(role.FlagsValue.ToString()) ? "0" : role.FlagsValue.ToString(),
            });

            if (id <= 0) return null;

            role.Id = id;
            return role;
        }

        public Role GetRole(int id)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT * FROM Isaac_Joy_Identity_Role WHERE Id = @Id");

            return Database.Instence.Query<Role>(sbSql.ToString(), new
            {
                Id = id
            }).FirstOrDefault();
        }

        public bool EditRole(Role role)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("UPDATE Isaac_Joy_Identity_Role");
            sbSql.AppendLine("SET");
            sbSql.AppendLine("  Name=@Name");
            sbSql.AppendLine("  ,Value=@Value");
            sbSql.AppendLine("WHERE");
            sbSql.AppendLine("  Id=@Id");

            var rowcount = Database.Instence.Execute(sbSql.ToString(), new
            {
                Name = role.Name ?? "",
                Value = string.IsNullOrEmpty(role.FlagsValue.ToString()) ? "0" : role.FlagsValue.ToString(),
                Id = role.Id,
            });

            return rowcount > 0;
        }

        public int DeleteRole(int[] ids)
        {
            var strid = ids.Aggregate(string.Empty, (current, id) => current + (id + ", "));

            var sbSql = new StringBuilder();
            sbSql.AppendLine(String.Format("UPDATE Isaac_Joy_Identity_Role SET IsDelete = 1 WHERE Id IN ({0} 0)", strid));

            return Database.Instence.Execute(sbSql.ToString());
        }

        public IEnumerable<Role> QueryRole(int pageindex, int pagesize, out int datacount)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT COUNT(1) ");
            sbSql.AppendLine("FROM Isaac_Joy_Identity_Role WHERE IsDelete = 0");

            datacount = Database.Instence.ExecuteScalar<int>(sbSql.ToString());

            sbSql.Clear();

            sbSql.AppendLine("SELECT");
            sbSql.AppendLine("  #Role.*");
            sbSql.AppendLine("FROM");
            sbSql.AppendLine("(");
            sbSql.AppendLine("  SELECT");
            sbSql.AppendLine("    Isaac_Joy_Identity_Role.*,");
            sbSql.AppendLine("    ROW_NUMBER() OVER (ORDER BY Isaac_Joy_Identity_Role.Id) AS [Row]");
            sbSql.AppendLine("  FROM Isaac_Joy_Identity_Role");
            sbSql.AppendLine(") as #Role");
            sbSql.AppendLine("WHERE IsDelete = 0");
            sbSql.AppendLine("  AND #Role.[Row] BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize");
            sbSql.AppendLine("ORDER BY #Role.[Row]");

            return Database.Instence.Query<Role>(
                sbSql.ToString(),
                new
                {
                    PageIndex = pageindex,
                    PageSize = pagesize
                });
        }

        public IEnumerable<Permission> GetPermissions()
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT * FROM Isaac_Joy_Identity_Permission");

            return Database.Instence.Query<Permission>(sbSql.ToString());
        }

        public Permission GetPermission(string code, string plugin)
        {
            var sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT * FROM Isaac_Joy_Identity_Permission WHERE Code = @Code AND PluginName=@PluginName");

            return Database.Instence.Query<Permission>(sbSql.ToString(), new
            {
                Code = code,
                PluginName = plugin
            }).FirstOrDefault();
        }
    }
}
